SHOW PLAN is an SQL statement that displays the execution plan of the previous SELECT statement. The plan gets generated and stored during the actual execution, so profiling must be enabled in the current session before running that statement. That can be done with a SET profiling=1 statement.
To view query execution plan in JSON queries, add "profile":true to the query. The result appears as a profile property in the result set.
- SQL
- HTTP
📋
SET profiling=1;
SELECT id FROM forum WHERE MATCH('i me') LIMIT 1;
SHOW PLAN;Response
Query OK, 0 rows affected (0.00 sec)
+--------+
| id |
+--------+
| 406443 |
+--------+
1 row in set (1.52 sec)
+------------------+----------------------------------------------------------------------+
| Variable | Value |
+------------------+----------------------------------------------------------------------+
| transformed_tree | AND(
AND(KEYWORD(i, querypos=1)),
AND(KEYWORD(me, querypos=2))) |
+------------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)In some cases the evaluated query tree can be rather different from the original one because of expansions and other transformations.
- SQL
- HTTP
📋
SET profiling=1;
SELECT id FROM forum WHERE MATCH('@title way* @content hey') LIMIT 1;
SHOW PLAN;Response
Query OK, 0 rows affected (0.00 sec)
+--------+
| id |
+--------+
| 711651 |
+--------+
1 row in set (0.04 sec)
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable | Value |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| transformed_tree | AND(
OR(
OR(
AND(fields=(title), KEYWORD(wayne, querypos=1, expanded)),
OR(
AND(fields=(title), KEYWORD(ways, querypos=1, expanded)),
AND(fields=(title), KEYWORD(wayyy, querypos=1, expanded)))),
AND(fields=(title), KEYWORD(way, querypos=1, expanded)),
OR(fields=(title), KEYWORD(way*, querypos=1, expanded))),
AND(fields=(content), KEYWORD(hey, querypos=2))) |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)See also EXPLAIN QUERY. It displays the execution tree of a full-text query without actually executing the query.
query property contains the transformed fulltext query tree. Each node contains:
type: node type. Can beAND,OR,PHRASE,KEYWORDetc.description: query subtree for this node shown as a string (inSHOW PLANformat)children: child nodes, if anymax_field_pos: maximum position within a fieldword: transformed keyword. Keyword nodes only.querypos: position of this keyword in a query. Keyword nodes only.excluded: keyword excluded from query. Keyword nodes only.expanded: keyword added by prefix expansion. Keyword nodes only.field_start: keyword must occur at the very start of the field. Keyword nodes only.field_end: keyword must occur at the very end of the field. Keyword nodes only.boost: keyword IDF will be multiplied by this. Keyword nodes only.
Index settings and status
SHOW INDEX STATUS is an SQL statement that displays various per-index statistics.
The syntax is:
SHOW INDEX index_name STATUSDisplayed statistics include:
indexed_documentsandindexed_bytes: number of the documents indexed and their text size in bytes, respectively.field_tokens_XXX: sums of per-field lengths (in tokens) over the entire index (that is used internally inBM25AandBM25Ffunctions for ranking purposes). Only available for indexes built withindex_field_lengths=1.ram_bytes: total size (in bytes) of the RAM-resident index portion.query_time_*: query execution time statistics of last 1 minute, 5 minutes, 15 minutes and total since server start; data is encapsulated as a JSON object which includes the number of queries and min, max, avg, 95 and 99 percentile values.found_rows_*: statistics of rows found by queries; provided for last 1 minute, 5 minutes, 15 minutes and total since server start; data is encapsulated as a JSON object which includes the number of queries and min, max, avg, 95 and 99 percentile values.
- SQL
- PHP
📋
mysql> SHOW INDEX products STATUS;Response
+--------------------------+--------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------------------------------------------------+
| index_type | rt |
| indexed_documents | 3 |
| indexed_bytes | 0 |
| ram_bytes | 6678 |
| disk_bytes | 611 |
| ram_chunk | 990 |
| ram_chunk_segments_count | 2 |
| disk_chunks | 0 |
| mem_limit | 134217728 |
| ram_bytes_retired | 0 |
| tid | 15 |
| tid_saved | 8 |
| query_time_1min | {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001} |
| query_time_5min | {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001} |
| query_time_15min | {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001} |
| query_time_total | {"queries":1, "avg_sec":0.001, "min_sec":0.001, "max_sec":0.001, "pct95_sec":0.001, "pct99_sec":0.001} |
| found_rows_1min | {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3} |
| found_rows_5min | {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3} |
| found_rows_15min | {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3} |
| found_rows_total | {"queries":1, "avg":3, "min":3, "max":3, "pct95":3, "pct99":3} |
+--------------------------+--------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)